import pymysql

conn = None
cursor = None
try:
    conn = pymysql.connect(host='192.168.174.132', port=3306, user='root', password='iTest_2022_mysql',
                           database='School',
                           charset='utf8')
    cursor = conn.cursor()

    drop_create_sql_1 = "drop table if exists classes;"
    cursor.execute(drop_create_sql_1)
    conn.commit()

    create_table_sql_1 = "create table if not exists classes (\
        id int unsigned auto_increment primary key not null,\
        name varchar(30) not null\
    );"
    cursor.execute(create_table_sql_1)
    conn.commit()

    insert_sql_1 = "insert into classes values(0,'ceshi_01期'),(0,'ceshi_02期');"
    cursor.execute(insert_sql_1)
    conn.commit()

    drop_create_sql_2 = "drop table if exists students;"
    cursor.execute(drop_create_sql_2)
    conn.commit()

    create_table_sql_2 = "create table if not exists students(\
        id int unsigned primary key auto_increment not null,\
        name varchar(20) default '',\
        age tinyint unsigned default 0,\
        height decimal(5,2),\
        gender enum('男','女','中性','保密') default '保密',\
        cls_id int unsigned default 0,\
        is_delete bit default 0\
    );"
    cursor.execute(create_table_sql_2)
    conn.commit()

    insert_sql_2 = "insert into students values\
    (0,'小明',18,180.00,2,1,0),\
    (0,'小月月',18,180.00,2,2,1),\
    (0,'彭于晏',29,185.00,1,1,0),\
    (0,'刘德华',59,175.00,1,2,1);"
    cursor.execute(insert_sql_2)
    conn.commit()

    select_sql_1 = "select * from students where is_delete = 0;"
    cursor.execute(select_sql_1)
    resp1 = cursor.fetchall()
    for i in resp1:
        print(i)

    print('-' * 30)
    select_sql_2 = "select * from students inner join classes on students.cls_id = classes.id where students.is_delete = 0 and classes.name = 'ceshi_01期';"
    cursor.execute(select_sql_2)
    resp2 = cursor.fetchall()
    for i in resp2:
        print(i)

    print('-' * 30)
    select_sql_3 = "select * from students where students.age > (select avg(age) from students);"
    cursor.execute(select_sql_3)
    resp3 = cursor.fetchall()
    for i in resp3:
        print(i)
except Exception as err:
    print("错误信息:", err)
    conn.rollback()
finally:
    cursor.close()
    conn.close()

# (1, '小明', 18, Decimal('180.00'), '女', 1, b'\x00')
# (3, '彭于晏', 29, Decimal('185.00'), '男', 1, b'\x00')
# ------------------------------
# (1, '小明', 18, Decimal('180.00'), '女', 1, b'\x00', 1, 'ceshi_01期')
# (3, '彭于晏', 29, Decimal('185.00'), '男', 1, b'\x00', 1, 'ceshi_01期')
# ------------------------------
# (4, '刘德华', 59, Decimal('175.00'), '男', 2, b'\x01')
